package inet.db.tennis;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import inet.db.pool.*;
import inet.util.Logger;

public class TennisGameDAO {

	private Logger logger = null;
	private DBPoolX poolX = null;
	public TennisGameDAO(){
		logger = new Logger(this.getClass().getName());
		try{
			poolX = DBPoolX.getInstance(DBPoolXName.SERVICE_BONGDA);
		}catch(Exception exception){
			logger.log(exception.toString()); 
		}
	}
	
	/**
	 * function get a TennisGame by id
	 * @param id
	 * @return
	 */
    public TennisGame getRow(BigDecimal id) {
        Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        String strSQL = null;
        TennisGame game = null;
        try {
            conn = poolX.getConnection();
            strSQL =
                "select ID, MATCH_ID, SCORE1, SCORE2, SET_ID, SERVE, TYPE, GEN_DATE, LAST_UPDATED " +
                "from TENNIS_GAME where ID = ? ";
            preStmt = conn.prepareStatement(strSQL);
            preStmt.setBigDecimal(1, id);
            rs = preStmt.executeQuery();
            if (rs.next()) {
            	game = new TennisGame();
            	game.setId(rs.getBigDecimal(1));
            	game.setMatchId(rs.getBigDecimal(2));
            	game.setScore1(rs.getInt(3));
            	game.setScore2(rs.getInt(4));
            	game.setSetId(rs.getBigDecimal(5));
            	game.setServe(rs.getInt(6));
            	game.setType(rs.getInt(7));
            	game.setGenDate(rs.getTimestamp(8));
            	game.setLastUpdated(rs.getTimestamp(9));
            }
        }
        catch (SQLException e) {
            logger.error("getRow: Error executing SQL " + strSQL + ">>>" + e.toString());
        } finally {
            poolX.releaseConnection(conn, preStmt, rs);
        }
        return game;
    }
    
    /**
     * function get last record of match.
     * @param matchId
     * @return
     */
    public TennisGame getLastRecord(BigDecimal matchId) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisGame game = null;
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			"select ID, MATCH_ID, SCORE1, SCORE2, SET_ID, SERVE, TYPE, GEN_DATE, LAST_UPDATED " +
    			"from TENNIS_GAME where MATCH_ID = ? order by ID desc ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setBigDecimal(1, matchId);
    		rs = preStmt.executeQuery();
    		if (rs.next()) {
    			game = new TennisGame();
    			game.setId(rs.getBigDecimal(1));
    			game.setMatchId(rs.getBigDecimal(2));
    			game.setScore1(rs.getInt(3));
    			game.setScore2(rs.getInt(4));
    			game.setSetId(rs.getBigDecimal(5));
    			game.setServe(rs.getInt(6));
    			game.setType(rs.getInt(7));
    			game.setGenDate(rs.getTimestamp(8));
    			game.setLastUpdated(rs.getTimestamp(9));
    		}
    	}
    	catch (SQLException e) {
    		logger.error("getRow: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return game;
    }
    
    /**
     * function get first player serve in set. 
     * ( Hàm trả về tay vợt giao bóng đầu tiên trong 1 SET đấu)
     * @param setId
     * @return 1 if player 1 serve else return 2 (player 2 serve)
     */
    public int getFirstPlayerServeInSet(BigDecimal setId) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	int serve = 1;
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			" select SERVE from TENNIS_GAME where SET_ID = ? order by ID desc, GEN_DATE desc ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setBigDecimal(1, setId);
    		rs = preStmt.executeQuery();
    		if (rs.next()) {
    			serve = rs.getInt(1);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("getFirstPlayerServeInSet: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return serve;
    }
    
    /**
     * fucntion get all TennisGame
     * @return Vector 
     * TennisGame (Full)
     */
    public Vector findAll() {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisGame game = null;
    	Vector vGame = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			"select ID, MATCH_ID, SCORE1, SCORE2, SET_ID, SERVE, TYPE, GEN_DATE, LAST_UPDATED " +
                "from TENNIS_GAME order by ID desc, GEN_DATE desc ";
    		preStmt = conn.prepareStatement(strSQL);
    		rs = preStmt.executeQuery();
    		vGame = new Vector();
    		while (rs.next()) {
    			game = new TennisGame();
            	game.setId(rs.getBigDecimal(1));
            	game.setMatchId(rs.getBigDecimal(2));
            	game.setScore1(rs.getInt(3));
            	game.setScore2(rs.getInt(4));
            	game.setSetId(rs.getBigDecimal(5));
            	game.setServe(rs.getInt(6));
            	game.setType(rs.getInt(7));
            	game.setGenDate(rs.getTimestamp(8));
            	game.setLastUpdated(rs.getTimestamp(9));
    			
            	vGame.addElement(game);
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findAll: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vGame;
    }
    
    /**
     * function get detail event of set
     * @param setId
     * @return Vector TennisGameDetail
     */
    public Vector findDetailSet(BigDecimal setId) {
    	Connection conn = null;
    	PreparedStatement preStmt = null;
    	ResultSet rs = null;
    	String strSQL = null;
    	TennisGameDetail detail = null;
    	Vector vGameDetail = null;
    	
    	try {
    		conn = poolX.getConnection();
    		strSQL =
    			"select a.SET_ID, a.SCORE1, a.SCORE2, a.SERVE, a.TYPE, a.ID, b.SCORE1 as GAME_POINT1, b.SCORE2 as GAME_POINT2, " +
    			"b.SPECIAL_POINT, b.GEN_DATE, b.SERVE as EVENT_SERVE " +
    			"from TENNIS_GAME a left join TENNIS_EVENT b on a.ID = b.GAME_ID  " +
    			"where a.SET_ID = ? order by GAME_ID, b.ID ";
    		preStmt = conn.prepareStatement(strSQL);
    		preStmt.setBigDecimal(1, setId);
    		rs = preStmt.executeQuery();
    		vGameDetail = new Vector();
    		BigDecimal gameId = BigDecimal.ZERO;
    		BigDecimal gameId1 = BigDecimal.ZERO;
    		String breakPoint = "<span class=\"ball-type-text breakball\" title=\"Break point\">BP</span>";
    		String setPoint = "<span class=\"ball-type-text setball\" title=\"Set point\">SP</span>";
    		String matchPoint = "<span class=\"ball-type-text matchball\" title=\"Match point\">MP</span>";
    		String breakPointSetPoint = "<span class=\"ball-type-text breakball\" title=\"Break point\">BP</span><span class=\"ball-type-text setball\" title=\"Set point\">SP</span>";
    		String event = "";
    		while (rs.next()) {
    			if(rs.getBigDecimal(6) == null) continue;
    			if(rs.getInt(5) == TennisGame.TYPE_GAME) {
	    			if(!gameId.equals(rs.getBigDecimal(6))) {
	    				detail = new TennisGameDetail();
	        			detail.setSetId(rs.getBigDecimal(1));
	        			detail.setScore1(rs.getInt(2));
	        			detail.setScore2(rs.getInt(3));
	        			detail.setServe(rs.getInt(4));
	        			detail.setType(rs.getInt(5));
	        			detail.setGameId(rs.getBigDecimal(6));
	        			detail.setGenDate(rs.getTimestamp(10));
	        			gameId = rs.getBigDecimal(6);
	        			if(!BigDecimal.ZERO.equals(gameId)){
	        				vGameDetail.addElement(detail);
	        			}
	    			}
	    			if(rs.getString(7) != null)
	    				event = rs.getString(7)+" : "+rs.getString(8);
	    			else 
	    				event = "";
	    			if(rs.getInt(9) == TennisEvent.SPECIAL_BREAK_POINT) {
	    				event += breakPoint;
	    			} else if(rs.getInt(9) == TennisEvent.SPECIAL_SET_POINT) {
	    				event += setPoint;
	    			} else if(rs.getInt(9) == TennisEvent.SPECIAL_MATCH_POINT) {
	    				event += matchPoint;
	    			} else if(rs.getInt(9) == TennisEvent.SPECIAL_BREAK_POINT_SET_POINT) {
	    				event += breakPointSetPoint;
	    			}
	    			detail.setListEvent(detail.getListEvent() != null ? detail.getListEvent()+", "+event:event);
    			} else {
    				if(!gameId.equals(rs.getBigDecimal(6))) {
    					detail = new TennisGameDetail();
	        			detail.setSetId(rs.getBigDecimal(1));
	        			detail.setScore1(rs.getInt(2));
	        			detail.setScore2(rs.getInt(3));
	        			detail.setServe(rs.getInt(4));
	        			detail.setType(rs.getInt(5));
	        			detail.setGameId(rs.getBigDecimal(6));
	        			detail.setGenDate(rs.getTimestamp(10));
	        			gameId = rs.getBigDecimal(6);
	        			vGameDetail.addElement(detail);
    				}
    				detail = new TennisGameDetail();
    				detail.setSetId(rs.getBigDecimal(1));
        			detail.setScore1(rs.getInt("GAME_POINT1"));
        			detail.setScore2(rs.getInt("GAME_POINT2"));
        			detail.setServe(rs.getInt("EVENT_SERVE"));
        			detail.setSpecialPoint(rs.getInt("SPECIAL_POINT"));
        			detail.setType(rs.getInt(5));
        			detail.setGameId(rs.getBigDecimal(6));
        			detail.setGenDate(rs.getTimestamp(10));
        			gameId = rs.getBigDecimal(6);
        			vGameDetail.addElement(detail);
    			}
    		}
    	}
    	catch (SQLException e) {
    		logger.error("findDetailSet: Error executing SQL " + strSQL + ">>>" + e.toString());
    	} finally {
    		poolX.releaseConnection(conn, preStmt, rs);
    	}
    	return vGameDetail;
    }
	
    /**
     * function insert full data to Tennis Game.
     * @param matchId
     * @param setId
     * @param score1
     * @param score2
     * @param serve
     * @param type
     * @return new Id if insert success else return ZERO.
     */
	public BigDecimal insertRow(BigDecimal matchId, BigDecimal setId, int score1, int score2, int serve, int type){
		Connection conn = null;
        PreparedStatement preStmt = null;
        ResultSet rs = null;
        String strSQL = null;
        BigDecimal id = BigDecimal.ZERO;
		BigDecimal result = BigDecimal.ZERO;
		
        try {
            conn = poolX.getConnection();
            strSQL = "SELECT TENNIS_GAME_SEQ.nextval FROM dual";
			preStmt = conn.prepareStatement(strSQL);
			rs = preStmt.executeQuery();
			if (rs.next()) {
				id = rs.getBigDecimal(1);
			}
			preStmt.close();
			
        	strSQL =" insert into TENNIS_GAME( ID, MATCH_ID, SET_ID, SCORE1, SCORE2, SERVE, TYPE) " +
    				" values(?, ?, ?, ?, ?, ?, ?) ";
	        preStmt = conn.prepareStatement(strSQL);
	        preStmt.setBigDecimal(1, id);
	        preStmt.setBigDecimal(2, matchId);
	        preStmt.setBigDecimal(3, setId);
	        preStmt.setInt(4, score1);
	        preStmt.setInt(5, score2);
	        preStmt.setInt(6, serve);
	        preStmt.setInt(7, type);
	        
	        if (preStmt.executeUpdate()>=1) {
	        	result = id;
	        }
        } catch (Exception ex) {
        	logger.error("insertRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
        } finally {
        	poolX.releaseConnection(conn, preStmt);
       }
       return result;
	}
	
	/**
	 * function insert options data to Tennis Game
	 * @param matchId
	 * @param setId
	 * @param serve
	 * @param type
	 * @return Id if insert success else return BigDecimal.ZERO.
	 */
	public BigDecimal insertRow(BigDecimal matchId, BigDecimal setId, int serve, int type){
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		BigDecimal id = BigDecimal.ZERO;
		BigDecimal result = BigDecimal.ZERO;
		
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL = "SELECT TENNIS_GAME_SEQ.nextval FROM dual";
			preStmt = conn.prepareStatement(strSQL);
			rs = preStmt.executeQuery();
			if (rs.next()) {
				id = rs.getBigDecimal(1);
			}
			preStmt.close();
			
			strSQL =" insert into TENNIS_GAME( ID, MATCH_ID, SET_ID, SERVE, TYPE) " +
			" values(?, ?, ?, ?, ?) ";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setBigDecimal(1, id);
			preStmt.setBigDecimal(2, matchId);
			preStmt.setBigDecimal(3, setId);
			preStmt.setInt(4, serve);
			preStmt.setInt(5, type);
			
			if (preStmt.executeUpdate()>=1) {
				result = id;
			}
		} catch (Exception ex) {
			logger.error("insertRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
	
	/**
	 * function update Score1 and score2 of GAME.
	 * @param id
	 * @param score1
	 * @param score2
	 * @return true if update success else return false.
	 */
	public boolean updateScore(BigDecimal id, int score1, int score2){
		Connection conn = null;
        PreparedStatement preStmt = null;
        boolean result = false;
        String strSQL = null;
        try {
        	conn = poolX.getConnection();
        	strSQL =" update TENNIS_GAME set SCORE1 = ?, SCORE2 = ?, LAST_UPDATED = sysdate " +
        			" where ID = ?";
        	preStmt = conn.prepareStatement(strSQL);
        	preStmt.setInt(1, score1);
        	preStmt.setInt(2, score2);
        	preStmt.setBigDecimal(3, id);
        	
        	if(preStmt.executeUpdate() > 0){
        		result = true;
        	}
        } catch (Exception ex) {
        	logger.error("updateScore: Error executing SQL " + strSQL + ">>>" + ex.toString());
        } finally {
        	poolX.releaseConnection(conn, preStmt);
       }
        return result;
	}
	
	/**
	 * function update player serve of game 
	 * @param id
	 * @param serve
	 * @return true if update success else return false.
	 */
	public boolean updateServe(BigDecimal id, int serve){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" update TENNIS_GAME set SERVE = ?, LAST_UPDATED = sysdate " +
			" where ID = ?";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setInt(1, serve);
			preStmt.setBigDecimal(2, id);
			
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("updateServe: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
	
	/**
	 * function update Score1, score2 and serve of GAME.
	 * @param id
	 * @param score1
	 * @param score2
	 * @param serve
	 * @return true if update success else return false.
	 */
	public boolean updateScore(BigDecimal id, int score1, int score2, int serve){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" update TENNIS_GAME set SCORE1 = ?, SCORE2 = ?, SERVE = ?, LAST_UPDATED = sysdate " +
			" where ID = ?";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setInt(1, score1);
			preStmt.setInt(2, score2);
			preStmt.setInt(3, serve);
			preStmt.setBigDecimal(4, id);
			
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("updateScore: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
	
	/**
	 * function delete a Tennis GAME
	 * @param id
	 * @return true if update success else return false.
	 */
	public boolean deleteRow(BigDecimal id){
		Connection conn = null;
		PreparedStatement preStmt = null;
		boolean result = false;
		String strSQL = null;
		try {
			conn = poolX.getConnection();
			strSQL =" delete from TENNIS_GAME where ID = ? ";
			preStmt = conn.prepareStatement(strSQL);
			preStmt.setBigDecimal(1, id);
			if(preStmt.executeUpdate() > 0){
				result = true;
			}
		} catch (Exception ex) {
			logger.error("deleteRow: Error executing SQL " + strSQL + ">>>" + ex.toString());
		} finally {
			poolX.releaseConnection(conn, preStmt);
		}
		return result;
	}
}
